# @Time: 2020/10/09 10:31
# @Author: may
# @Desc:
import time
from decimal import Decimal

from selenium.webdriver.common.by import By
from data_entry.data_entry import DataEntry


def deduplication(value, is_number=1):
    """去掉字符串中重复的值"""

    val = value.split(',')[:-1]
    val = list(set(val))
    if len(val) == 1:
        if is_number:
            value = str(Decimal(val[0]).quantize(Decimal('0.00')))
        else:
            value = val[0]
    else:
        value = val
    return value


class RealEstateIncome(DataEntry):
    """房地产收入"""

    # 点击菜单
    def click_real_estate_income(self):
        self.click((By.XPATH, "//div[contains(@class,'partOne fdcsrIcon')]//div"))
        self.switch_to_data_entry()

    def create_houses(self, number_of_buildings, number_of_unit, number_of_floor, floor_height, number_of_households,
                      house_type, building_structure, decoration_type, building_id, area01, area02, area03,
                      house_type_underground01, number_of_underground_households01, underground_area01,
                      underground_floor_height01, house_type_underground02, number_of_underground_households02,
                      underground_area02, underground_floor_height02):
        """新建房源信息"""

        self.click_real_estate_income()
        # 点击新建房源信息按钮
        self.click((By.XPATH, "//div[contains(@class,'drpz jcssf xjfyxxIcon fdcsrBtn')]//p"))
        time.sleep(1)
        # 进入新建房源iframe
        iframe_create_house = self.find_element((By.XPATH, '//iframe[1]'))
        self.switch_to_frame(iframe_create_house)

        # 地上
        # 楼栋数
        self.send_keys((By.NAME, 'lou'), number_of_buildings)
        # 楼单元数
        self.send_keys((By.NAME, 'danyuan'), number_of_unit)
        # 楼层数
        self.send_keys((By.NAME, 'ceng'), number_of_floor)
        # 层高
        self.send_keys((By.ID, 'xjfycenggao'), floor_height)
        # 户数
        self.send_keys((By.NAME, 'hushu'), number_of_households)
        # 房屋类型
        loc_house_type = (By.XPATH, "//td[1]//div[1]//div[1]//input[1]")
        self.click(loc_house_type)
        # 2：普通住宅，3：其他住宅，依次类推
        self.click((By.XPATH,
                    f"//div[contains(@class,'layui-unselect layui-form-select layui-form-selected')]//dd["
                    f"{house_type}]"))
        house_type = self.get_attribute(loc_house_type)
        # 建筑结构
        loc_building_structure = (By.XPATH, "//td[2]//div[1]//div[1]//input[1]")
        self.click(loc_building_structure)
        # 2：砖木结构，3：框—筒结构，依次类推
        self.click((By.XPATH,
                    f"//div[contains(@class,'layui-unselect layui-form-select layui-form-selected')]//dd["
                    f"{building_structure}]"))
        building_structure = self.get_attribute(loc_building_structure)
        # 装修类型
        loc_decoration_type = (By.XPATH, "//td[3]//div[1]//div[1]//input[1]")
        self.click(loc_decoration_type)
        # 2：高档装修，3：精装，依次类推
        self.click((By.XPATH,
                    f"//div[contains(@class,'layui-unselect layui-form-select layui-form-selected')]//dd["
                    f"{decoration_type}]"))
        decoration_type = self.get_attribute(loc_decoration_type)
        # 楼号
        self.send_keys((By.NAME, 'title'), building_id)
        # 面积1
        self.send_keys((By.XPATH, "//tr[1]//td[2]//div[1]//input[1]"), area01)
        # 面积2
        self.send_keys((By.XPATH, "//tr[2]//td[2]//div[1]//input[1]"), area02)
        # 面积3
        self.send_keys((By.XPATH, "//tr[3]//td[2]//div[1]//input[1]"), area03)

        # 地下
        # 添加一条
        self.click((By.LINK_TEXT, '添加一条'))
        # 地下1
        # 房屋类型01
        loc_house_type_underground01 = (By.XPATH, '//tr[1]//td[1]//div[1]//div[1]//div[1]//input[1]')
        self.click(loc_house_type_underground01)
        # 3：不可售公共配套设施
        self.click((By.XPATH, f'//tr[1]//dd[{house_type_underground01}]'))
        house_type_underground01 = self.get_attribute(loc_house_type_underground01)
        # 层户数01
        self.send_keys((By.XPATH, '//tr[1]//td[3]//div[1]//input[1]'), number_of_underground_households01)
        # 每户面积1
        self.send_keys((By.XPATH, '//tr[1]//td[4]//div[1]//input[1]'), underground_area01)
        # 层高01
        underground_floor_height01 = self.send_keys((By.XPATH, '//tr[1]//td[5]//div[1]//input[1]'),
                                                    underground_floor_height01)
        # 地下2
        # 房屋类型02
        loc_house_type_underground02 = (By.XPATH, '//tr[2]//td[1]//div[1]//div[1]//div[1]//input[1]')
        self.click(loc_house_type_underground02)
        # 3：不可售公共配套设施
        self.click((By.XPATH, f'//tr[2]//dd[{house_type_underground02}]'))
        house_type_underground02 = self.get_attribute(loc_house_type_underground02)
        # 层户数02
        self.send_keys((By.XPATH, '//tr[2]//td[3]//div[1]//input[1]'), number_of_underground_households02)
        # 每户面积1
        self.send_keys((By.XPATH, '//tr[2]//td[4]//div[1]//input[1]'), underground_area02)
        # 层高02
        underground_floor_height02 = self.send_keys((By.XPATH, '//tr[2]//td[5]//div[1]//input[1]'),
                                                    underground_floor_height02)
        data = [number_of_buildings, number_of_unit, number_of_floor, floor_height, number_of_households,
                house_type, building_structure, decoration_type, building_id, area01, area02, area03,
                house_type_underground01, number_of_underground_households01, underground_area01,
                underground_floor_height01, house_type_underground02, number_of_underground_households02,
                underground_area02, underground_floor_height02]
        # 保存
        self.click((By.XPATH, "//body/div[@class='shaixuanContral']/a[1]"))
        time.sleep(1)
        self.click((By.XPATH, "//div[@id='lcsqr']//a[@class='saveBlue rightHandel']"))

    def find_building_by_taxpayer_id(self, project_id):
        """根据纳税人识别号查询房源数量"""

        conn = self.get_conn()
        cursor = conn.cursor()
        sql = 'SELECT count(*) FROM Lou t WHERE t.Xmid = ?'
        with conn:
            cursor.execute(sql, project_id)
            row = cursor.fetchone()[0]
        return row

    def get_building_info(self, project_id):
        """根据纳税人识别号查询房源信息"""

        conn = self.get_conn()
        cursor = conn.cursor()
        sql1 = """ 
                    IF exists(SELECT *
                              FROM sys.objects
                              WHERE type = 'P'
                                AND name = 'getBuildingInfo')
                    DROP PROCEDURE dbo.getBuildingInfo;       
            """
        sql2 = """
                    CREATE PROCEDURE getBuildingInfo @project_id VARCHAR(25)
                    AS
                    BEGIN
                        DECLARE
                            @number_of_buildings VARCHAR(10),
                            @number_of_unit VARCHAR(10),
                            @number_of_floor VARCHAR(10),
                            @floor_height VARCHAR(10),
                            @number_of_households VARCHAR(10),
                            @house_type VARCHAR(500),
                            @building_structure VARCHAR(50),
                            @decoration_type VARCHAR(50),
                            @building_id VARCHAR(50),
                            @area01 VARCHAR(500),
                            @area02 VARCHAR(500),
                            @area03 VARCHAR(500),
                            @house_type_underground01 VARCHAR(500),
                            @number_of_underground_households01 VARCHAR(10),
                            @underground_area01 VARCHAR(500),
                            @underground_floor_height01 VARCHAR(50),
                            @house_type_underground02 VARCHAR(500),
                            @number_of_underground_households02 VARCHAR(50),
                            @underground_area02 VARCHAR(500),
                            @underground_floor_height02 VARCHAR(50)
                        SET @house_type = '';SET @area01 = '';SET @area02 = '';SET @area03 = '';SET 
                        @house_type_underground01 = '';
                        SET @underground_area01 = '';SET @underground_floor_height01 = '';SET 
                        @house_type_underground02 = ''
                        SET @underground_area02 = ''SET @underground_floor_height02 = '';
                        -- 地上
                        -- 楼栋数
                        SELECT @number_of_buildings = count(*) FROM Lou t WHERE t.Xmid = @project_id;
                        -- 楼单元数
                        SELECT @number_of_unit = count(*) FROM Danyuan t WHERE t.Danyuan_xmid = @project_id;

                        -- 楼层数
                        SELECT @number_of_floor = count(*)
                            FROM Louceng t
                            WHERE exists(SELECT 1 FROM Fangwu f WHERE f.loucengGuid = t.loucengGuid AND f.IsDS = '1')
                              AND t.Louceng_xmid = @project_id;
                        -- 层高
                        SELECT @floor_height = t.lcHeight FROM Louceng t WHERE t.Louceng_xmid = @project_id;
                        -- 每单元层户数
                        SELECT @number_of_households = count(*)
                            FROM Fangwu t
                            WHERE t.Fangwu_xmid = @project_id
                              AND t.IsDS = '1'
                            GROUP BY t.loucengGuid;
                        -- 房屋类型
                        SELECT @house_type = @house_type + ZD.FW_MC + ','
                            FROM Fangwu t
                                     INNER JOIN ZDfwlx ZD ON t.FW_DM = ZD.FW_DM
                            WHERE t.Fangwu_xmid = @project_id
                              AND t.IsDS = '1';
                        -- 建筑结构
                        SELECT @building_structure = z.JzjgMc
                            FROM Lou t
                                     INNER JOIN Zdjzjglx z ON z.JzjgDm = t.JglxDm
                            WHERE t.Xmid = @project_id;
                        -- 装修类型
                        SELECT @decoration_type = z.ZxMc
                            FROM Lou t
                                     INNER JOIN Zdzxlx z ON z.ZxDm = t.ZxlxDm
                            WHERE t.Xmid = @project_id;
                        -- 楼号
                        SELECT @building_id = t.louName FROM lou t WHERE t.Xmid = @project_id;
                        -- 面积01
                        SELECT @area01 = @area01 + CONVERT(VARCHAR(50), t.mianji) + ','
                            FROM Fangwu t
                            WHERE t.Fangwu_xmid = @project_id
                              AND t.fangwuName LIKE '%01'
                              AND t.IsDS = '1';
                        -- 面积02
                        SELECT @area02 = @area02 + CONVERT(VARCHAR(50), t.mianji) + ','
                            FROM Fangwu t
                            WHERE t.Fangwu_xmid = @project_id
                              AND t.fangwuName LIKE '%02'
                              AND t.IsDS = '1';
                        -- 面积03
                        SELECT @area03 = @area03 + CONVERT(VARCHAR(50), t.mianji) + ','
                            FROM Fangwu t
                            WHERE t.Fangwu_xmid = @project_id
                              AND t.fangwuName LIKE '%03'
                              AND t.IsDS = '1';

                        -- 地下
                        -- 地下1
                        -- 房屋类型01
                        SELECT @house_type_underground01 = @house_type_underground01 + ZD.FW_MC + ','
                            FROM Fangwu t
                                     INNER JOIN ZDfwlx ZD ON t.FW_DM = ZD.FW_DM
                            WHERE t.Fangwu_xmid = @project_id
                              AND t.IsDS = '-1'
                              AND t.fangwuName LIKE '-10%';
                        -- 层户数01
                        SELECT @number_of_underground_households01 = count(*)
                            FROM Fangwu t
                            WHERE t.Fangwu_xmid = @project_id
                              AND t.IsDS = '-1'
                              AND t.fangwuName LIKE '-10%'
                            GROUP BY t.loucengGuid;
                        -- 每户面积01
                        SELECT @underground_area01 = @underground_area01 + CONVERT(VARCHAR(50), t.mianji) + ','
                            FROM Fangwu t
                            WHERE t.Fangwu_xmid = @project_id
                              AND t.IsDS = '-1'
                              AND t.fangwuName LIKE '-10%';
                        -- 层高01
                        SELECT @underground_floor_height01 = @underground_floor_height01 + CONVERT(VARCHAR(50), 
                        t.lcHeight) + ','
                            FROM Louceng t
                            WHERE t.Louceng_xmid = @project_id
                              AND t.loucengName = '-1';
                        -- 地下2
                        -- 房屋类型02
                        SELECT @house_type_underground02 = @house_type_underground02 + ZD.FW_MC + ','
                            FROM Fangwu t
                                     INNER JOIN ZDfwlx ZD ON t.FW_DM = ZD.FW_DM
                            WHERE t.Fangwu_xmid = @project_id
                              AND t.IsDS = '-1'
                              AND t.fangwuName LIKE '-20%';
                        -- 层户数02
                        SELECT @number_of_underground_households02 = count(*)
                            FROM Fangwu t
                            WHERE t.Fangwu_xmid = @project_id
                              AND t.IsDS = '-1'
                              AND t.fangwuName LIKE '-20%'
                            GROUP BY t.loucengGuid;
                        -- 每户面积02
                        SELECT @underground_area02 = @underground_area02 + CONVERT(VARCHAR(50), t.mianji) + ','
                            FROM Fangwu t
                            WHERE t.Fangwu_xmid = @project_id
                              AND t.IsDS = '-1'
                              AND t.fangwuName LIKE '-20%';
                        -- 层高02
                        SELECT @underground_floor_height02 = @underground_floor_height02 + CONVERT(VARCHAR(50), 
                        t.lcHeight) + ','
                            FROM Louceng t
                            WHERE t.Louceng_xmid = @project_id
                              AND t.loucengName = '-2';

                        SELECT @number_of_buildings                number_of_buildings,
                               @number_of_unit                     number_of_unit,
                               @number_of_floor                    number_of_floor,
                               @floor_height                       floor_height,
                               @number_of_households               number_of_households,
                               @house_type                         house_type,
                               @building_structure                 building_structure,
                               @decoration_type                    decoration_type,
                               @building_id                        building_id,
                               @area01                             area01,
                               @area02                             area02,
                               @area03                             area03,
                               @house_type_underground01           house_type_underground01,
                               @number_of_underground_households01 number_of_underground_households01,
                               @underground_area01                 underground_area01,
                               @underground_floor_height01         underground_floor_height01,
                               @house_type_underground02           house_type_underground02,
                               @number_of_underground_households02 number_of_underground_households02,
                               @underground_area02                 underground_area02,
                               @underground_floor_height02         underground_floor_height02
                    END
                    """
        with conn:
            cursor.execute(sql1)
            cursor.execute(sql2)
            cursor.execute('EXECUTE getBuildingInfo ?', project_id)
            row = cursor.fetchone()
            row[5] = deduplication(row[5], 0)
            row[9] = deduplication(row[9])
            row[10] = deduplication(row[10])
            row[11] = deduplication(row[11])
            row[12] = deduplication(row[12], 0)
            row[14] = deduplication(row[14])
            row[15] = deduplication(row[15])
            row[16] = deduplication(row[16], 0)
            row[18] = deduplication(row[18])
            row[19] = deduplication(row[19])
        return row
